import pandas as pd
import matplotlib.pyplot as plt
plt.rcParams['font.family'] = 'SimHei'  # 或者 'Arial Unicode MS'
import json


# 读取B表数据
b_data = pd.read_excel('b表-脱敏.xls')
b_column = '金额'

# 将"金额"列转换为浮点数类型
b_data[b_column] = pd.to_numeric(b_data[b_column], errors='coerce')

# 计算B表汇总
b_total = b_data[b_column].sum()

# 统计B表中不同金额的出现频次
b_counts = b_data[b_column].value_counts().sort_index(ascending=False).to_dict()

# 读取A表数据
a_data = pd.read_csv('a表-脱敏.csv', skiprows=4)
a_column = '入款'

# 过滤A表数据，选择出款为0且摘要不为'划款'的行
a_filtered_data = a_data[(a_data['出款'] == '0') & (a_data['摘要'] != '划款')]

# 将"金额"列转换为浮点数类型
# for i in range(len(a_filtered_data[a_column])):
#     a_filtered_data[a_column][i] = float(a_filtered_data[a_column][i])

# a_filtered_data[a_column] = float(a_filtered_data[a_column])
a_filtered_data[a_column] = pd.to_numeric(a_filtered_data[a_column].str.replace(',',''), errors='coerce')

# 计算A表汇总
a_total = a_filtered_data[a_column].sum()

# 统计A表中不同金额的出现频次
a_counts = a_filtered_data[a_column].value_counts().sort_index(ascending=False).to_dict()

# 读取C表数据
c_data = pd.read_excel('c表-脱敏.xls')
c_column = '实际支付金额'

# 将"实际支付金额"列转换为浮点数类型
c_data[c_column] = pd.to_numeric(c_data[c_column], errors='coerce')

# 计算C表汇总
c_total = c_data[c_column].sum()

# 统计C表中不同金额的出现频次
c_counts = c_data[c_column].value_counts().sort_index(ascending=False).to_dict()

# 打印A表统计
print("A表统计:")
print("A表汇总:", a_total)
a_data = json.dumps(a_counts, indent=4)
# print("A表统计频次:", A_data)

# 打印B表统计
print("B表统计:")
print("B表汇总:", b_total)
b_data = json.dumps(b_counts, indent=4)
# print("B表统计频次:", B_data)

# 打印C表统计
print("C表统计:")
print("C表汇总:", c_total)
c_data = json.dumps(c_counts, indent=4)
# print("C表统计频次:", C_data)

# 创建DataFrame用于存储数据
df = pd.DataFrame(columns=['key', 'A_value', 'B_value', 'C_value'])

# 合并所有的键，并按照浮点数大小排序
keys = sorted(set(a_counts.keys()) | set(b_counts.keys()) | set(c_counts.keys()), key=float)

# 将数据添加到DataFrame中
for key in keys:
    a_value = a_counts.get(key, 0)
    b_value = b_counts.get(key, 0)
    c_value = c_counts.get(key, 0)
    row = {'key': key, 'A_value': a_value, 'B_value': b_value, 'C_value': c_value}
    df = pd.concat([df, pd.DataFrame(row, index=[0])], ignore_index=True)

# 创建Excel写入器
writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')

# 将DataFrame写入Excel
df.to_excel(writer, index=False, sheet_name='Sheet1')

# 获取Excel工作簿和工作表对象
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# 设置黄色填充样式
yellow_fill = workbook.add_format({'bg_color': 'yellow'})

# 检查每行数据的条件并设置单元格样式
for row in range(1, len(df) + 1):
    a_value = df.loc[row - 1, 'A_value']
    b_value = df.loc[row - 1, 'B_value']
    c_value = df.loc[row - 1, 'C_value']
    if a_value + b_value != c_value:
        worksheet.conditional_format(row, 0, row, 0, {'type': 'no_errors', 'format': yellow_fill})

# 保存Excel文件
writer._save()
